﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DataAccess;
using System.IO;
using System.Diagnostics;

namespace CreatePGSQLDataScript
{
    public partial class DBCompare : Form
    {
        public DBCompare()
        {
            InitializeComponent();
        }
        int timesValue = 0;
        DataAccess.PgSqlProvider db1;
        DataAccess.PgSqlProvider db2;
        System.Timers.Timer timer2 = new System.Timers.Timer();

        List<ProcInfo> ListProcInfo1 = new List<ProcInfo>();
        List<ProcInfo> ListProcInfo2 = new List<ProcInfo>();

        DataTable dtParamterColl1 = new DataTable();
        DataTable dtParamterColl2 = new DataTable();

        DataTable dtReturnColl1 = new DataTable();
        DataTable dtReturnColl2 = new DataTable();

        private void StructScript_Load(object sender, EventArgs e)
        {
            historyList.ReadList(cmbConn1);
            historyList.ReadList(cmbConn2);
            dataGridView1.AutoGenerateColumns = false;
            timer2.Interval = 1000;
            timer2.Elapsed += new System.Timers.ElapsedEventHandler(timer2_Elapsed);
            //BindSysObject();
        }

        private void LoadAllTable(PgSqlProvider db, List<ProcInfo> ListProcInfo, ProgressBar pb1)
        {
            string sqlText = "select relname from pg_class where relkind='r' and relnamespace=2200 and relname like '" + textBox1.Text.Trim() + "' order by relname ";
            DataTable dt = db.GetDataTable(sqlText);

            pb1.Maximum = dt.Rows.Count;

            foreach (DataRow dr in dt.Rows)
            {
                string tableName = dr["relname"].ToString();
                string Context = LoadTable(tableName, db);
                ListProcInfo.Add(new ProcInfo() { procName = tableName, procContext = Context });

                pb1.Value++;
                ResponseUI(pb1.Maximum, pb1.Value);
            }
        }


        private string LoadTable(string tableName, PgSqlProvider db)
        {
            string sqlText = string.Format(@"
                        select a.column_name,a.data_type,a.character_maximum_length,numeric_precision,
                        a.numeric_scale, a.is_nullable,d.description,a.column_default 
                        from information_schema.columns as a
                        inner join pg_class as c on a.table_name=c.relname and a.ordinal_position>=1 and a.table_schema='public'
                        left join pg_description as d on d.objoid=c.oid and d.objsubid=a.ordinal_position
                        where c.relname='{0}' order by a.ordinal_position", tableName);

            DataTable dt = db.GetDataTable(sqlText);

            string tableSchema = @"
CREATE TABLE {0}
(
{1}
)
WITH (OIDS=FALSE);
ALTER TABLE {0} OWNER TO postgres;
{2}
";
            string columns = "";
            string descripts = "";

            foreach (DataRow dr in dt.Rows)
            {
                //列信息 nullInfo，defalutValue 如果存在的話，前面加上空格
                string ctype = getType(dr);
                string cDescipt = dr["description"].ToString();
                string nullInfo = dr["is_nullable"].ToString() == "NO" ? " NOT NULL" : "";
                string cDefault = dr["column_default"].ToString();
                string cName = dr["column_name"].ToString();

                string defalutValue = cDefault == "" ? "" : " DEFAULT " + cDefault;
                string SingleColumn = "\t\"" + cName + "\" " + ctype + nullInfo + defalutValue + ",";

                //列描述信息
                if (cDescipt != "")
                {
                    descripts += "COMMENT ON COLUMN " + tableName + ".\"" + cName + "\" IS " + "'" + cDescipt + "';\n";
                    SingleColumn += " --" + cDescipt;
                }
                SingleColumn += "\n";
                columns += SingleColumn;

            }

            string Constraint = LoadConstraint(tableName, db);
            columns += Constraint;
            columns = columns.Substring(0, columns.Length - 1);

            string script = string.Format(tableSchema, tableName, columns, descripts);
            return script;



        }

        private string LoadConstraint(string tableName, PgSqlProvider db)
        {
            string sqlText = @"
                        select c.conname,contype,conkey from pg_constraint as c
                        inner join pg_class as o on c.conrelid=o.oid
                        where contype='p' and o.relname='comp'
                        ";
            DataTable dt = db.GetDataTable(sqlText);
            if (dt.Rows.Count <= 0)
                return "";
            string conname = dt.Rows[0]["conname"].ToString();
            string conkeys = ConvertList((short[])dt.Rows[0]["conkey"]);

            sqlText = string.Format(@"select column_name from information_schema.columns 
                                    where table_name='{0}' and ordinal_position in({1})
                                    order by ordinal_position", tableName, conkeys);
            dt = db.GetDataTable(sqlText);

            string keys = "";
            foreach (DataRow dr in dt.Rows)
            {
                if (keys != "")
                {
                    keys += "," + dr["column_name"].ToString();
                }
                else
                {
                    keys += dr["column_name"].ToString();
                }
            }

            string constraint = string.Format("\tCONSTRAINT {0} PRIMARY KEY ({1}),", conname, keys);
            return constraint;

        }

        private string ConvertList(short[] list1)
        {
            if (list1.Length <= 0)
                return "";
            string value = "";
            foreach (short i in list1)
            {
                value += "," + i.ToString();
            }
            value = value.Substring(1);
            return value;
        }


        private string getType(DataRow dr)
        {
            string displayType = "";
            switch (dr["data_type"].ToString())
            {
                case "character varying":
                    displayType = "character varying(" + dr["character_maximum_length"].ToString() + ")";
                    break;
                case "numeric":
                    displayType = "numeric(" + dr["numeric_precision"].ToString() + "," + dr["numeric_scale"].ToString() + ")";
                    break;
                default:
                    displayType = dr["data_type"].ToString();
                    break;
            }
            displayType = displayType.Replace("()", "");
            return displayType;
        }



        /// <summary>
        /// 裝載函數
        /// </summary>
        private void LoadDBObject(PgSqlProvider db, List<ProcInfo> ListProcInfo, ProgressBar pb1)
        {
            string sqlText = string.Format(@"select proname,proargtypes,
                                            coalesce(prosrc,'') as prosrc
                                            ,proargnames,pronargs,pg_type.typname
                                            from pg_proc 
                                            left join pg_type on pg_type.oid=pg_proc.prorettype
                                            where prolang = '16386' limit 1000");

            DataTable dt = db.GetDataTable(sqlText);

            pb1.Maximum = dt.Rows.Count;

            foreach (DataRow dr in dt.Rows)
            {

                string procName = dr["proname"].ToString();
                //string paramter = getParamters(procName, dtParamer);
                string paramter = getParam(dr["proargtypes"].ToString(), dr["proargnames"]);

                //string returnInfo = getReturnInfo(procName, dtReturnInfo);
                string returnInfo = getReturnInfo(dr["typname"].ToString());

                StringBuilder sb = new StringBuilder();

                sb.Append(string.Format("CREATE OR REPLACE FUNCTION {0}({1})", procName, paramter));
                sb.Append(returnInfo);
                sb.Append(dr["prosrc"].ToString());
                sb.Append("$$ LANGUAGE 'plpgsql'");
                dr["proname"] = sb.ToString();
                ListProcInfo.Add(new ProcInfo { procName = procName, procContext = sb.ToString() });
                pb1.Value++;

                ResponseUI(pb1.Maximum, pb1.Value);

            }
            //DataSet ds = new DataSet();
            //ds.Tables.Add(dt);
            //ds.WriteXml("d:\\bb.xml");


        }

        private void LoadAllView(PgSqlProvider db, List<ProcInfo> ListProcInfo, ProgressBar pb1)
        {

            string sqlText = "select  viewname,definition from pg_views where schemaname='public'";

            //string sqlText = "select table_name,view_definition from  information_schema.views where table_schema='public'";
            DataTable dt = db.GetDataTable(sqlText);
            pb1.Maximum = dt.Rows.Count;

            foreach (DataRow dr in dt.Rows)
            {

                



                string viewName = dr["viewname"].ToString();

                string aa = "";
                if (viewName == "v_apuser")
                {
                    string cc = dr["definition"].ToString();
                    
                    for (int i = 0; i < cc.Length; i++)
                    {
                        aa += cc[i] + "/" + Microsoft.VisualBasic.Strings.Asc(cc[i]).ToString();
                    }




                }
















                string Context = string.Format(@"
CREATE OR REPLACE VIEW {0} AS 
    {1}
ALTER TABLE {0} OWNER TO postgres;", viewName, dr["definition"].ToString());

                ListProcInfo.Add(new ProcInfo { procName = viewName, procContext = Context });
                pb1.Value++;

                ResponseUI(pb1.Maximum, pb1.Value);
            }




        }

        private void ResponseUI(int maxValue, int currValue)
        {
            int modValue = maxValue / 100;
            modValue = modValue == 0 ? 1 : modValue;
            if (currValue % modValue == 0 || currValue == maxValue)
            {
                System.Threading.Thread.Sleep(1);
                Application.DoEvents();
            }
        }


        private string getReturnInfo(string datatype)
        {
            if (datatype == "record")
                datatype = "setof record";

            return "\nRETURNS " + datatype + " AS \n$$ ";
        }






        private void btnLoad_Click(object sender, EventArgs e)
        {


            //加进度条
            ListProcInfo1 = new List<ProcInfo>();
            ListProcInfo2 = new List<ProcInfo>();
            StartTimes();


            foreach (var item1 in chkListItemType.CheckedItems)
            {
                if (item1.ToString() == "數據表")
                {
                    progressBar1.Value = 0;
                    progressBar2.Value = 0;
                    LoadAllTable(db1, ListProcInfo1, progressBar1);
                    LoadAllTable(db2, ListProcInfo2, progressBar2);
                }
                if (item1.ToString() == "函數")
                {
                    progressBar1.Value = 0;
                    progressBar2.Value = 0;
                    LoadDBObject(db1, ListProcInfo1, progressBar1);
                    LoadDBObject(db2, ListProcInfo2, progressBar2);
                }
                if (item1.ToString() == "視圖")
                {
                    progressBar1.Value = 0;
                    progressBar2.Value = 0;
                    LoadAllView(db1, ListProcInfo1, progressBar1);
                    LoadAllView(db2, ListProcInfo2, progressBar2);
                }
            }



            StopTimes();
            Compare();
        }


        private void Compare()
        {
            var coll = (from obj1 in ListProcInfo1
                        join obj2 in ListProcInfo2 on obj1.procName equals obj2.procName into Coll
                        from obj2 in Coll.DefaultIfEmpty()
                        where obj1.procContext != (obj2 != null ? obj2.procContext : "")
                        select new
                        {
                            objName1 = obj1.procName,
                            objName2 = obj2 != null ? obj2.procName : "N/A",
                            Context1 = obj1.procContext,
                            Context2 = obj2 != null ? obj2.procContext : "N/A"
                        })
                           .Union(
                           from obj2 in ListProcInfo2
                           join obj1 in ListProcInfo1 on obj2.procName equals obj1.procName into Coll
                           from obj1 in Coll.DefaultIfEmpty()
                           where obj1 == null
                           select new
                           {
                               objName1 = "N/A",
                               objName2 = obj2 != null ? obj2.procName : "N/A",
                               Context1 = "N/A",
                               Context2 = obj2 != null ? obj2.procContext : "N/A"
                           });


            dataGridView1.DataSource = coll.ToList();

        }

        /// <summary>
        /// 保存到 XML
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSave_Click(object sender, EventArgs e)
        {


        }


        public string getParam(string types, object obj)
        {
            string[] namelist = { };
            if (obj != DBNull.Value)
            {
                namelist = (string[])obj;
            }
            types = types.Replace("1043", "varchar");
            types = types.Replace("23", "int");
            types = types.Replace("1700", "numeric");

            string[] typelist = types.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

            string parmeall = "";
            for (int i = 0; i < typelist.Length; i++)
            {
                if (parmeall != "")
                {
                    parmeall += "," + typelist[i] + " " + namelist[i];
                }
                else
                {
                    parmeall += typelist[i] + " " + namelist[i];
                }

            }
            return parmeall;

        }



        private void createFile(string txt1, string txt2)
        {
            File.WriteAllText(@"D:\pgsqlTEMP\a.txt", txt1);
            File.WriteAllText(@"D:\pgsqlTEMP\b.txt", txt2);
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.Columns[e.ColumnIndex].Name == "view")
            {
                string txt1 = dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[2].Value.ToString();
                string txt2 = dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[3].Value.ToString();

                string bcompareexe = @"C:\Program Files\Beyond Compare\BCompare.exe";
                string file1 = @"D:\pgsqlTEMP\a.txt";
                string file2 = @"D:\pgsqlTEMP\b.txt";


                createFile(txt1, txt2);

                Process run = new Process();
                run.StartInfo.Arguments = string.Format("\"{0}\" \"{1}\"", file1, file2);
                run.StartInfo.FileName = string.Format("\"{0}\"", bcompareexe);
                run.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
                run.StartInfo.CreateNoWindow = true;
                run.Start();
            }

        }

        void timer2_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            this.Invoke(new Action(updateUI));
        }

        private void updateUI()
        {
            timesValue++;
            lbTimes.Text = timesValue.ToString();
            System.Threading.Thread.Sleep(10);
            Application.DoEvents();

        }

        private void StartTimes()
        {
            timesValue = 0;
            timer2.Enabled = true;
        }
        private void StopTimes()
        {
            timer2.Enabled = false;
        }

        private void btnConn1_Click(object sender, EventArgs e)
        {
            //cmbConn1.Items.Add("server=192.168.1.58;database=justgogopg11;uid=postgres;pwd=`12345;MaxPoolSize=500;");
            db1 = new DataAccess.PgSqlProvider(cmbConn1.Text);
            db1.Open();
            historyList.SaveValue(cmbConn1);
            MessageBox.Show("连接成功！");
        }

        private void btnConn2_Click(object sender, EventArgs e)
        {
            //cmbConn2.Items.Add("server=192.168.3.5;database=justgogopg11;uid=postgres;pwd=`12345;MaxPoolSize=500;");
            db2 = new DataAccess.PgSqlProvider(cmbConn2.Text);
            db2.Open();
            historyList.SaveValue(cmbConn2);
            MessageBox.Show("连接成功！");
        }

    }

    public class ProcInfo
    {
        public string procName;
        public string procContext;
    }


}
//public class ParamInfo
//{
//    public string specific_name;
//    public string parameter_name;
//    public string data_type;
//}

//public class ReturnInfo
//{
//    public string routine_name;
//    public string data_type;
//}

//        private void BindSysObject()
//        {
//            string sqlText = @"select proname,
//                                coalesce(prosrc,'') as prosrc
//                                ,proargnames
//                                from pg_proc 
//                                where prolang = '16386'";
//            DataTable dt= db1.GetDataTable(sqlText);
//            lboxProc1.DisplayMember = "proname";
//            lboxProc1.ValueMember = "proname";
//            lboxProc1.DataSource = dt;
//        }

//            string sqlText = string.Format(@"
//                        select data_type
//                        from information_schema.routines
//                        where specific_schema = 'public'
//                        and routine_name = '{0}' limit 1", procName);

//            string sqlText=string.Format( @"
//                        select parameter_name,data_type
//                        from information_schema.parameters 
//                        where specific_schema = 'public' 
//                        and specific_name ~ '{0}_[0-9]*$' 
//                        order by ordinal_position",tableName);




//        private string getParamters(string tableName, DataTable dtParamer)
//        {
//            DataRow[] drColl= dtParamer.Select(string.Format("specific_name like '{0}%'",tableName));

//            string paramters = "";
//            foreach (DataRow dr in drColl)
//            {
//                paramters += dr["parameter_name"].ToString() + " " + dr["data_type"].ToString() +",";
//            }
//            if (paramters != "")
//            {
//                paramters = paramters.Substring(0, paramters.Length - 1);
//            }
//            return paramters;

//        }



//        private string getReturnInfo(string procName, DataTable dtReturnInfo)
//        {

//            DataRow[] drColl = dtReturnInfo.Select(string.Format("routine_name='{0}'", procName));
//            string datatype = drColl[0][0].ToString();
//            if (datatype == "record")
//                datatype = "setof record";

//            return "\nRETURNS " + datatype + " AS \n$$ ";


//        }

//        private DataTable  LoadParamterColl(PgSqlProvider db)
//        {
//            string sqlText = @"
//                        select specific_name,parameter_name,data_type
//                        from information_schema.parameters 
//                        where specific_schema = 'public' and parameter_name!=''
//                        order by specific_name,ordinal_position";
//            DataTable dt = db.GetDataTable(sqlText);
//            return dt;
//        }

//        public DataTable  LoadReturnColl(PgSqlProvider db)
//        {
//            string sqlText = @"
//                        select routine_name,data_type
//                        from information_schema.routines
//                        where specific_schema = 'public'";
//            DataTable dt= db.GetDataTable(sqlText);
//            return dt;
//        }
